package cn.com.huzo.service.view.purchase;

import cn.com.huzo.model.entities.purchase.NotesPayabledHeaderEntity;
import cn.com.huzo.model.entities.purchase.NotesPayabledLinesEntity;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

import static org.apache.poi.ss.util.CellUtil.getCell;
import org.springframework.web.servlet.view.document.AbstractXlsView;

/**
 * 导出对账单Excel
 * <p>
 * @author 范学春
 */
@SuppressWarnings("unchecked")
public class NotesPayabledExcelView extends AbstractXlsView {

    public NotesPayabledExcelView() {
        super();
    }

    @Override
    protected Workbook createWorkbook(Map<String, Object> model, HttpServletRequest request) {
        HSSFWorkbook workbook = null;
        try (InputStream _tmp = new FileInputStream(request.getServletContext().getRealPath("/") + "/WEB-INF/templates/03_purchase/对账单（模版）.xls")) {
            workbook = new HSSFWorkbook(_tmp);
        } catch (FileNotFoundException ex) {
            //Logger.getLogger(ProductionPlanningExcelView.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            //Logger.getLogger(ProductionPlanningExcelView.class.getName()).log(Level.SEVERE, null, ex);
        }

        return workbook;
    }

    /**
     *
     * @param model 文件数据
     * @param workbook 基于对账单（模版）.xls创建的excel文件
     * @param request 客户端请求
     * @param response 客户端响应
     * @throws Exception
     */
    @Override
    protected void buildExcelDocument(Map<String, Object> model,
            Workbook workbook, HttpServletRequest request,
            HttpServletResponse response)
            throws Exception {
        int _startRow = 4;   // 起始明细行

        // 显示对话框提示用户下载或打开
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename=" + new String("对账单.xls".getBytes(), "ISO8859-1"));

        // 从模版取得第一个sheet
        Sheet sheet = workbook.getSheetAt(0);

        // 边框样式
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setBorderBottom(BorderStyle.THIN);     // 下边框    
        cellStyle.setBorderLeft(BorderStyle.THIN);       // 左边框    
        cellStyle.setBorderTop(BorderStyle.THIN);        // 上边框    
        cellStyle.setBorderRight(BorderStyle.THIN);      // 右边框 

        // 边框样式
        CellStyle cellStyle1 = workbook.createCellStyle();
        cellStyle1.setBorderBottom(BorderStyle.THIN);     // 下边框    
        cellStyle1.setBorderLeft(BorderStyle.THIN);       // 左边框    
        cellStyle1.setBorderTop(BorderStyle.THIN);        // 上边框    
        cellStyle1.setBorderRight(BorderStyle.THIN);      // 右边框 
        Font font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 10);
        font.setBold(true);         //字体增粗
        //把字体应用到当前的样式
        cellStyle1.setFont(font);

        // 报表头
        Object oo = model.get("HeaderEntity");
        NotesPayabledHeaderEntity headerentity = (NotesPayabledHeaderEntity) oo;
        
        getCell(sheet.getRow(0), 1).setCellValue((String) model.get("EnterpriseName"));//显示贸易公司
        
        getCell(sheet.getRow(1), 1).setCellValue((String) headerentity.getSupplierName());
        getCell(sheet.getRow(1), 9).setCellValue((new SimpleDateFormat("yyyy-MM-dd")).format(headerentity.getCycleOfBillFrom()) + " ～ " + (new SimpleDateFormat("yyyy-MM-dd")).format(headerentity.getCycleOfBillTo()));

        // 明细
        Object obj = model.get("LinesData");
        List<NotesPayabledLinesEntity> lineslist = (List<NotesPayabledLinesEntity>) obj;

        DecimalFormat df = new DecimalFormat("0.###");       //小数位3位 
        DecimalFormat df02 = new DecimalFormat("0.##");      //小数位2位 
        DecimalFormat df04 = new DecimalFormat("0.####");    //小数位4位 
        double _total = 0.00;  // 总计
        for (NotesPayabledLinesEntity item : lineslist) {
            // 创建新行          
            sheet.shiftRows(_startRow, sheet.getLastRowNum(), 1, true, false);
            Row newRow = sheet.createRow(_startRow);
            Row sourceRow = sheet.getRow(_startRow + 1);
            for (int j = 0; j < sourceRow.getLastCellNum(); j++) {
                Cell oldCell = sourceRow.getCell(j);
                Cell newCell = newRow.createCell(j);
                if (oldCell == null) {
                    continue;
                }
                newCell.setCellStyle(oldCell.getCellStyle());
            }
            // 明细行
            if (item.getRowFlag() == 1 || item.getRowFlag() == 3) {
                if (item.getArrivalDate() != null) {
                    getCell(sheet.getRow(_startRow), 0).setCellValue((new SimpleDateFormat("yyyy-MM-dd")).format(item.getArrivalDate()));
                }
                getCell(sheet.getRow(_startRow), 1).setCellValue(item.getStyleNo());
                getCell(sheet.getRow(_startRow), 2).setCellValue(item.getMaterialName());
                if (item.getMaterialCode() != null) {
                    getCell(sheet.getRow(_startRow), 3).setCellValue(item.getMaterialCode());
                }
                if (item.getSpecification() != null) {
                    getCell(sheet.getRow(_startRow), 4).setCellValue(item.getSpecification());
                }
                if (item.getMaterialColourName() != null) {
                    getCell(sheet.getRow(_startRow), 5).setCellValue(item.getMaterialColourName());
                }
                if (item.getUomName() != null) {
                    getCell(sheet.getRow(_startRow), 6).setCellValue(item.getUomName());
                }

                if (item.getPoQuantity() != null && item.getPoQuantity() != 0) {
                    getCell(sheet.getRow(_startRow), 7).setCellValue(Double.parseDouble(df.format(item.getPoQuantity())));
                }
                if (item.getStockInQuantity() != null && item.getStockInQuantity() != 0) {
                    getCell(sheet.getRow(_startRow), 8).setCellValue(Double.parseDouble(df.format(item.getStockInQuantity())));
                }
                if (item.getPayabledQuantity() != null && item.getPayabledQuantity() != 0) {
                    getCell(sheet.getRow(_startRow), 9).setCellValue(Double.parseDouble(df.format(item.getPayabledQuantity())));
                }
                if (item.getUnitPriceAddedTax() != null && item.getUnitPriceAddedTax() != 0) {
                    getCell(sheet.getRow(_startRow), 10).setCellValue(Double.parseDouble(df04.format(item.getUnitPriceAddedTax())));
                }
                if (item.getPayabledAmount() != null && item.getPayabledAmount() != 0) {
                    getCell(sheet.getRow(_startRow), 11).setCellValue(Double.parseDouble(df.format(item.getPayabledAmount())));
                }
                if (item.getInvoiceNo() != null) {
                    getCell(sheet.getRow(_startRow), 12).setCellValue(item.getInvoiceNo());
                }
                sheet.addMergedRegion(new CellRangeAddress(_startRow, _startRow, 12, 13));
                if (item.getTaxRate() != null) {
                    int _taxrate = (int) Math.floor(item.getTaxRate() * 100);
                    getCell(sheet.getRow(_startRow), 14).setCellValue(_taxrate + "%");
                }
                if (item.getLotNo() != null) {
                    getCell(sheet.getRow(_startRow), 15).setCellValue(item.getLotNo());
                }
                if (item.getDescription() != null) {
                    getCell(sheet.getRow(_startRow), 16).setCellValue(item.getDescription());
                }
                _total = _total + item.getPayabledAmount();
                //_total = Double.parseDouble(df02.format(_total));
            }
            // 小计行 
            /*
            if (item.getRowFlag() == 2) {
                CellStyle _newCellStyle = workbook.createCellStyle();
                _newCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                _newCellStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
                Font _font = workbook.createFont();
                _font.setFontName("宋体");
                _font.setBold(true);
                _font.setFontHeightInPoints((short) 10);//设置字体大小  
                _newCellStyle.setBorderBottom(BorderStyle.THIN);
                _newCellStyle.setBorderTop(BorderStyle.THIN);
                _newCellStyle.setBorderLeft(BorderStyle.THIN);
                _newCellStyle.setBorderRight(BorderStyle.THIN);
                _newCellStyle.setAlignment(HorizontalAlignment.RIGHT);
                _newCellStyle.setFont(_font);//选择需要用到的字体格式  
                DataFormat format = workbook.createDataFormat();
                _newCellStyle.setDataFormat(format.getFormat("#,##0.00"));
                sheet.addMergedRegion(new CellRangeAddress(_startRow, _startRow, 0, 6));
                getCell(sheet.getRow(_startRow), 8).setCellStyle(_newCellStyle);
                getCell(sheet.getRow(_startRow), 8).setCellValue(item.getStockInQuantity());
                getCell(sheet.getRow(_startRow), 9).setCellStyle(_newCellStyle);
                getCell(sheet.getRow(_startRow), 9).setCellValue(item.getPayabledQuantity());
                sheet.addMergedRegion(new CellRangeAddress(_startRow, _startRow, 10, 16));
            }*/
            if (item.getRowFlag() == 4) {
                CellStyle _newCellStyle = workbook.createCellStyle();
                _newCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                _newCellStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
                Font _font = workbook.createFont();
                _font.setFontName("宋体");
                _font.setBold(true);
                _font.setFontHeightInPoints((short) 10);//设置字体大小  
                _newCellStyle.setBorderBottom(BorderStyle.THIN);
                _newCellStyle.setBorderTop(BorderStyle.THIN);
                _newCellStyle.setBorderLeft(BorderStyle.THIN);
                _newCellStyle.setBorderRight(BorderStyle.THIN);
                _newCellStyle.setAlignment(HorizontalAlignment.RIGHT);
                _newCellStyle.setFont(_font);//选择需要用到的字体格式  
                DataFormat format = workbook.createDataFormat();
                _newCellStyle.setDataFormat(format.getFormat("#,##0.00"));
                sheet.addMergedRegion(new CellRangeAddress(_startRow, _startRow, 2, 6));
                getCell(sheet.getRow(_startRow), 1).setCellValue(item.getStyleNo());
                getCell(sheet.getRow(_startRow), 11).setCellStyle(_newCellStyle);
                getCell(sheet.getRow(_startRow), 11).setCellValue(item.getPayabledAmount());
                sheet.addMergedRegion(new CellRangeAddress(_startRow, _startRow, 12, 16));
            }
            // 合计行 
            if (item.getRowFlag() == 5) {
                CellStyle _newCellStyle = workbook.createCellStyle();
                _newCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                _newCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
                Font _font = workbook.createFont();
                _font.setFontName("宋体");
                _font.setBold(true);
                _font.setFontHeightInPoints((short) 12);//设置字体大小  
                _newCellStyle.setBorderBottom(BorderStyle.THIN);
                _newCellStyle.setBorderTop(BorderStyle.THIN);
                _newCellStyle.setBorderLeft(BorderStyle.NONE);
                _newCellStyle.setBorderRight(BorderStyle.NONE);
                _newCellStyle.setAlignment(HorizontalAlignment.LEFT);
                _newCellStyle.setFont(_font);//选择需要用到的字体格式  
                DataFormat format = workbook.createDataFormat();
                _newCellStyle.setDataFormat(format.getFormat("#,##0.00"));
                _newCellStyle.setAlignment(HorizontalAlignment.RIGHT);

                sheet.addMergedRegion(new CellRangeAddress(_startRow, _startRow, 0, 9));

                getCell(sheet.getRow(_startRow), 10).setCellStyle(_newCellStyle);
                //getCell(sheet.getRow(_startRow), 11).setCellStyle(_newCellStyle);
                if (item.getPayabledAmount() != null) {
                    getCell(sheet.getRow(_startRow), 10).setCellValue(item.getPayabledAmount());
                    //getCell(sheet.getRow(_startRow), 10).setCellValue(_total);
                }
                sheet.addMergedRegion(new CellRangeAddress(_startRow, _startRow, 10, 11));

                CellStyle _newCellStyle02 = workbook.createCellStyle();
                _newCellStyle02.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                _newCellStyle02.setFillForegroundColor(IndexedColors.WHITE.getIndex());
                _newCellStyle02.setBorderBottom(BorderStyle.THIN);
                _newCellStyle02.setBorderTop(BorderStyle.THIN);
                _newCellStyle02.setBorderLeft(BorderStyle.NONE);
                _newCellStyle02.setBorderRight(BorderStyle.NONE);
                Font _font01 = workbook.createFont();
                _font01.setFontName("宋体");
                _font01.setBold(false);
                _font01.setFontHeightInPoints((short) 12); // 设置字体大小  
                _newCellStyle02.setFont(_font01);          // 选择需要用到的字体格式  
                _newCellStyle02.setAlignment(HorizontalAlignment.LEFT);
                getCell(sheet.getRow(_startRow), 13).setCellStyle(_newCellStyle02);
                //getCell(sheet.getRow(_startRow), 13).setCellValue(headerentity.getUpdatedByName());  // 需要空出来

                CellStyle _newCellStyle03 = workbook.createCellStyle();
                _newCellStyle03.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                _newCellStyle03.setFillForegroundColor(IndexedColors.WHITE.getIndex());
                _newCellStyle03.setBorderBottom(BorderStyle.THIN);
                _newCellStyle03.setBorderTop(BorderStyle.THIN);
                _newCellStyle03.setBorderLeft(BorderStyle.NONE);
                _newCellStyle03.setBorderRight(BorderStyle.NONE);
                _newCellStyle03.setFont(_font01);//选择需要用到的字体格式  
                _newCellStyle03.setAlignment(HorizontalAlignment.CENTER);
                sheet.addMergedRegion(new CellRangeAddress(_startRow, _startRow, 12, 16));
                getCell(sheet.getRow(_startRow), 12).setCellStyle(_newCellStyle03);
                getCell(sheet.getRow(_startRow), 12).setCellValue((new SimpleDateFormat("yyyy.M.d")).format(headerentity.getUpdatedDate()));

            }
            _startRow = _startRow + 1;
        }

        // 删除最后空白行          
        sheet.shiftRows(_startRow + 1, sheet.getLastRowNum(), -1, true, false);
    }
}
